
import pandas as pd
import pandahouse as ph
import numpy as np
import pandas_profiling
import plotly.express as px
import datetime


connection_default = {'host': 'http://clickhouse.beslan.pro:8080',
                      'database':'default',
                      'user':'******', 
                      'password':'******'
                     }


'''К сожалению, данные в базе выглядят таким образом, что просто считать их не удалось. У столбцов с
датами необычный формат, даты имеют вид типа '2021-16-01' или '2021-8-01', множество пустых ячеек
и тип Nullable(String). В связи с чем пришлось считывать их как строки, используя функцию assumeNotNull.'''

q = '''
    SELECT CAST(assumeNotNull(date_order) as String) as date_order,
    number_order, CAST(planned_data_order as String) as planned_data_order,
    timeslot_number, CAST(assumeNotNull(result_data_order) as String) as result_data_order,
    delivery_type, carrier_name, seller_ID, order_status, customer_cluster,
    warehouse_cluster
    FROM 
        {db}.logistics_online
    '''


logistics_online = ph.read_clickhouse(query=q, connection=connection_default)
logistics_online.head()


'''Теперь заменим все пустые ячейки на NaN и преобразуем столбцы с датами к нужному формату.'''

logistics_online = logistics_online.replace('', np.nan)
logistics_online['date_order'] = pd.to_datetime(logistics_online['date_order'],
                                 errors='coerce', format='%Y-%d-%m')
logistics_online['result_data_order'] = pd.to_datetime(logistics_online['result_data_order'],
                                        errors='coerce', format='%Y-%d-%m')
logistics_online['planned_data_order'] = pd.to_datetime(logistics_online['planned_data_order'],
                                         errors='coerce', format='%Y-%d-%m')
logistics_online.head()


pandas_profiling.ProfileReport(logistics_online)

Summarize dataset:   0%|          | 0/25 [00:00<?, ?it/s]

Generate report structure:   0%|          | 0/1 [00:00<?, ?it/s]

Render HTML:   0%|          | 0/1 [00:00<?, ?it/s]


'''Да, действительно один и тот же заказ встречается трижды. К строке со вторым таймслотом вопросов
нет, заказ не смогли доставить в плановую дату с первой попытки и перенесли. А вот оставшиеся две строки
похожи на баг, они абсолютно идентичны. Удалим одну из них'''

logistics_online[logistics_online['number_order']==344629456]


logistics_online = logistics_online.drop_duplicates(keep = 'first')


'''Очень много NaN значений. Вероятно, причина в некорректном сборе данных, при наличии возможности 
нужно было бы разобраться, но у нас ее нет. Просто удалить все NaN нельзя, это почти 15% всех имеющихся данных.
Попробуем разобраться, что не так, исходя из имеющейся информации'''

logistics_online.isna().sum()

date_order            658
number_order            0
planned_data_order      0
timeslot_number         0
result_data_order     934
delivery_type           0
carrier_name            0
seller_ID               0
order_status            0
customer_cluster        0
warehouse_cluster       0
dtype: int64


'''Если просто посмотреть на NaN значения в столбце с датой заказа, видно, что они, как правило стоят у заказов
с таймслотами больше одного. Вероятно, когда заказ не успевают доставить и он переносится, дата заказа
по какой-то причине в системе повторно не проставляется. Во-первых, в контексте данного задания дата заказа
нас вообще не интересует. Во-вторых, это не багованные заказы, они доставлены и у них есть дата заказа там, где
заказ с этим же номером проходит с таймслотом номер 1. Поэтому мы вообще не будем их трогать. Все равно при
дальнейших расчетах нас будут интересовать только уникальные номера заказов, дубли номеров мы удалим и часть
этих заказов удалится.'''

logistics_online[logistics_online['date_order'].isna()].head()


'''Вот с датой доставки сложнее. Это как раз то, что нас интересует в данном задании. Посмотрим на статусы.
Заказы с отсутствующей датой доставки и статусами 'Отменен', 'На пути к клиенту', 'Ожидает сборки' можно
удалять, потому что нам нужны только доставленные. На статус 'Доставлен' нужно посмотреть.'''

logistics_online[logistics_online['result_data_order'].isna()].order_status.unique()

array(['Отменен', 'На пути к клиенту', 'Ожидает сборки', 'Доставлен'],
      dtype=object)


logistics_online[logistics_online['result_data_order'].isna()].query('order_status == "Доставлен"').head()


'''Возможно, просто баг в сборе данных. Но таких заказов всего 44 штуки, это менее процента от всего датафрейма.
Существенного влияния на результаты их удаление не окажет.'''

logistics_online[logistics_online['result_data_order'].isna()].query('order_status == "Доставлен"').shape

(44, 11)


logistics_without_nan = logistics_online[logistics_online['result_data_order'].notna()]


'''В итоге заказы с явно не интересующими нас статусами удалились. Статусы 'Доставлен', 'Отменен', 'Спор'
 вполне могут быть у реально доставленных получателю заказов (например, в случае, если покупатель вернул заказ 
 уже после доставки или у товара есть какой-то брак и покупатель заказ получил, но открыл спор), поэтому
 считаем данные готовыми к анализу'''

logistics_without_nan['order_status'].unique()

array(['Доставлен', 'Отменен', 'Спор'], dtype=object)


'''Для облегчения расчетов создадим дополнительный столбец, в котором напротив каждого заказа 
проставим 1, если он доставлен вовремя и 0 в противном случае. Тогда для того, чтобы рассчитать процент
вовремя доставленных заказов, нужно будет просто сгруппировать дф по нужным признакам и посчитать среднее
по этому столбцу.'''

logistics_without_nan['ontime'] = np.where(logistics_without_nan['result_data_order'] <= 
                                           logistics_without_nan['planned_data_order'],
                             1, 0)

C:\Users\79307\anaconda3\envs\python_3_7\lib\site-packages\ipykernel_launcher.py:8: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy


'''Отсортируем дф так, чтобы заказы шли по возрастанию итоговой даты доставки и по возрастанию таймслота,
затем удалим дубли номеров заказов, отавив только последний. Объясню логику. Подобная метрика действительно
существует у компании, я посмотрела это в руководстве для продавцов. Она считается сложнее, чем указано
в задании, но логика такая: берутся все заказы, доставленные в определенный день и считается процент доставленных
в пределах плановой даты от общего числа доставленных. Предположим, что один и тот же заказ
переносился дважды. Тогда он будет фигурировать в дф три раза. Плановая дата доставки будет меняться, как
и таймслот. Сама итоговая дата доставки у всех трех будет одинаковая. Во-первых, оставить все три я не могу.
Поскольку получится, что в одну дату доставки у нас два доставленных не вовремя и один доставленный
вовремя заказ. Это исказит результаты, поскольку заказ это на самом деле один и тот же. В задании не указано, 
что подразумевается под 'доставленным вовремя' заказом. Вовремя с какой попытки? Плановая дата постоянно
сдвигается. Уточнить возможности нет. Поэтому я решаю оставить только последнюю попытку доставки,
и сравнивать реальную дату доставки с плановой датой, которая была установлена последней'''

logistics_without_nan = logistics_without_nan.sort_values(['result_data_order', 'timeslot_number'])
logistics_online_unique = logistics_without_nan.drop_duplicates(subset = 'number_order', keep = 'last')


'''Получаем итоговый датафрейм. Из-за обилия информации выглядит не слишко наглядно, используем
возможности визуализации, чтобы посмотреть динамику. Непонятно в задании, подразумевается, что
разрезы "внутри друг друга" или же динамика в каждом разрезе отдельно. Оставим такой датафрейм и
отдельно сделаем в каждом разрезе'''

ontime = logistics_online_unique.groupby(['result_data_order', 'carrier_name',
                          'delivery_type', 'warehouse_cluster', 'timeslot_number']) \
                .agg({'ontime': 'mean'}).reset_index()
ontime['ontime'] = (ontime['ontime']*100)
ontime[ontime['result_data_order']=='2021-01-04']


'''Просто динамика по дням. Поскольку речь идет о процентах (метрика - отношение), а признаков очень много,
мы не можем строить графики по имеющемуся дф, данные будут некорректными. Для визуализации отдельно по каждому
разрезу придется создавать свой датафрейм'''

ontime_date = logistics_online_unique.groupby('result_data_order') \
              .agg({'ontime': 'mean'}).reset_index()
ontime_date['ontime'] = (ontime_date['ontime']*100)
ontime_date.head()


fig = px.line(ontime_date, x="result_data_order", y="ontime", 
              labels = dict(result_data_order="dates", ontime="ontime_delivered, %"))
fig.show()


'''Динамика по дням в разрезе перевозчиков'''

ontime_carrier = logistics_online_unique.groupby(['result_data_order', 'carrier_name']) \
              .agg({'ontime': 'mean'}).reset_index()
ontime_carrier['ontime'] = (ontime_carrier['ontime']*100)
ontime_carrier.head()


fig = px.line(ontime_carrier, x="result_data_order", y="ontime", color = 'carrier_name',
             labels = dict(result_data_order="dates", ontime="ontime_delivered, %"))
fig.show()


'''Динамика по дням в разрезе типа доставки'''

ontime_type = logistics_online_unique.groupby(['result_data_order', 'delivery_type']) \
              .agg({'ontime': 'mean'}).reset_index()
ontime_type['ontime'] = (ontime_type['ontime']*100)
ontime_type.head()


fig = px.line(ontime_type, x="result_data_order", y="ontime", color = 'delivery_type',
             labels = dict(result_data_order="dates", ontime="ontime_delivered, %"))
fig.show()


'''Динамика по дням в разрезе кластеров склада'''

online_cluster = logistics_online_unique.groupby(['result_data_order', 'warehouse_cluster']) \
              .agg({'ontime': 'mean'}).reset_index()
online_cluster['ontime'] = (online_cluster['ontime']*100)
online_cluster.head()


fig = px.line(online_cluster, x="result_data_order", y="ontime", color = 'warehouse_cluster',
             labels = dict(result_data_order="dates", ontime="ontime_delivered, %"))
fig.show()


'''Динамика по дням в разрезе таймслотов'''

online_timeslot = logistics_online_unique.groupby(['result_data_order', 'timeslot_number']) \
              .agg({'ontime': 'mean'}).reset_index()
online_timeslot['ontime'] = (online_timeslot['ontime']*100)
online_timeslot.head()


fig = px.line(online_timeslot, x="result_data_order", y="ontime", color = 'timeslot_number',
             labels = dict(result_data_order="dates", ontime="ontime_delivered, %"))
fig.show()


'''Возьмём датафрейм из первого задания. В нем уже удален один дубликат. Поскольку нас интересует
только первая плановая дата доставки, отфильтруем его по таймслоту номер 1.'''

first_delivery = logistics_online.query('timeslot_number == 1')
first_delivery.head()


'''Поскольку в дф остались только заказы с таймслотом номер 1, все номера заказов должны быть уникальными.
Но здесь видно, что осталось три дубликата. Посмотрим подробнее на каждый.'''

first_delivery[first_delivery.number_order.duplicated()]


first_delivery[first_delivery['number_order']==333776216]


first_delivery[first_delivery['number_order']==336693909]


'''Во всех трёх одинаковая ситуация. Они доставлены с первой попытки, абсолютно идентичны, отличается только
дата заказа на 1-2 дня. Возможно, заказы просто задвоились в системе.'''

first_delivery[first_delivery['number_order']==357469624]


'''Удалим дубли, оставив только первый, поскольку, я думаю, фактически заказ был сделан
в более раннюю дату.'''

first_delivery = first_delivery.drop_duplicates(subset = 'number_order', keep = 'first')


'''Также мы не сможем использовать для анализа заказы, в которых даты заказа нет'''

first_delivery[first_delivery['date_order'].isna()].head()


'''Их всего 33 штуки, можно их удалить'''

first_delivery[first_delivery['date_order'].isna()].shape

(33, 11)


'''Удаляем NaN и оставляем только нужные колонки'''

first_delivery = first_delivery[first_delivery['date_order'].notna()]
first_delivery = first_delivery[['date_order', 'planned_data_order',
                                'carrier_name', 'warehouse_cluster', 'customer_cluster']]
first_delivery.head()


'''Добавим колонку с обещанным сроком доставки в днях, а также с месяцем года и номером недели'''

first_delivery['duration'] = (first_delivery['planned_data_order'] - first_delivery['date_order']).dt.days
first_delivery['month'] = first_delivery['date_order'].dt.strftime('%Y-%m')
first_delivery['week'] = first_delivery['date_order'].apply(lambda x: x.strftime("%W"))
PromisedClick2Delivery = first_delivery
PromisedClick2Delivery.head()


'''Думаю, что для анализа динамики будет разумно использовать среднее значение показателя
обещанного срока доставки. Начнем с динамики по месяцам. Таблица небольшая, поэтому 
выглядит достаточно наглядно'''

month_PromisedClick2Delivery = PromisedClick2Delivery.groupby('month', as_index = False) \
                       .agg({'duration': 'mean'})
month_PromisedClick2Delivery


fig = px.line(month_PromisedClick2Delivery, x="month", y="duration",
             labels = dict(month="month", duration="avg_duration"))
fig.show()


'''Динамика по неделям'''

week_PromisedClick2Delivery = PromisedClick2Delivery.groupby('week', as_index = False) \
                       .agg({'duration': 'mean'})
week_PromisedClick2Delivery.head()


fig = px.line(week_PromisedClick2Delivery, x="week", y="duration",
             labels = dict(week="number of week", duration="avg_duration"))
fig.show()


'''Динамика по дням'''

days_PromisedClick2Delivery = PromisedClick2Delivery.groupby('date_order', as_index = False) \
                       .agg({'duration': 'mean'})
days_PromisedClick2Delivery.head()


fig = px.line(days_PromisedClick2Delivery, x="date_order", y="duration",
             labels = dict(date_order="date_order", duration="avg_duration"))
fig.show()


'''Сравнение перевозчиков'''

fig = px.box(PromisedClick2Delivery, x="carrier_name", y="duration")
fig.show()


'''Сравнение обещанного срока доставки из кластера в кластер. Стоило бы уточнить, в какой форме это хотели
бы увидеть, но, я думаю, таблицы здесь достаточно'''

PromisedClick2Delivery_clusters = PromisedClick2Delivery.groupby(['warehouse_cluster', 'customer_cluster']) \
                       .agg({'duration': 'mean'})
PromisedClick2Delivery_clusters.head()


'''Возьмем датафрейм из первого задания. В нем уже удалены недоставленые заказы и 44 доставленных,
дата доставки у которых отсутствует. '''

logistics_without_nan.head()


'''Выше мы уже разобрались, что дата заказа часто при изменении таймслота не проставляется.
Вот ниже для примера можно посмотреть. Но допустим, что где-то получилось наоборот и при первом таймслоте
даты доставки нет, а дальше где-то проставлена. Номера заказов нас интересуют уникальные, итоговая дата
доставки все равно проставляется для всех строк одна. Поэтому отсортируем дф по дате заказа, 
это 'опустит' строки, в которых даты заказа нет, вниз. Затем на всякий случай по таймслоту
и удалим дубликаты номеров заказов, оставив первый'''

logistics_without_nan[logistics_without_nan['number_order'] == 3514710433]


table_Click2Delivery = logistics_without_nan.sort_values(['date_order', 'timeslot_number']) \
.drop_duplicates(subset = 'number_order', keep = 'first')
table_Click2Delivery.head()


'''Все равно осталось 22 заказа с отсутствующей датой. Это немного, удалим их.'''

table_Click2Delivery.isna().sum()

date_order            22
number_order           0
planned_data_order     0
timeslot_number        0
result_data_order      0
delivery_type          0
carrier_name           0
seller_ID              0
order_status           0
customer_cluster       0
warehouse_cluster      0
ontime                 0
dtype: int64


'''И оставим только нужные колонки. Непонятно, о каких кластерах идет речь в задании,
оставим и кластеры склада, и кластеры покупателей.'''

table_Click2Delivery = table_Click2Delivery.dropna()
table_Click2Delivery = table_Click2Delivery[['date_order', 'number_order', 'result_data_order',
                                            'customer_cluster', 'warehouse_cluster']]
table_Click2Delivery.head()


'''Добавим колонку с фактическим сроком доставки в днях. 
Видим очень странную вещь, есть заказы, дата офрмления которых позже, чем их дата доставки.
Я посмотрела на них в изначальном датафрейме, да, все так. Даты распарсены верно, заказы уникальные,
мы ничего лишнего не удалили.'''

table_Click2Delivery['delivery_time'] = (table_Click2Delivery['result_data_order'] - 
                                        table_Click2Delivery['date_order']).dt.days
table_Click2Delivery.head()


'''Их много, почти 1000 штук. Но использовать их для анализа я не могу, отрицательные
значения исказят результат.'''

table_Click2Delivery[table_Click2Delivery['delivery_time'] < 0]


table_Click2Delivery = table_Click2Delivery[table_Click2Delivery['delivery_time'] >= 0]
table_Click2Delivery['month'] = table_Click2Delivery['date_order'].dt.strftime('%Y-%m')
table_Click2Delivery['week'] = table_Click2Delivery['date_order'].apply(lambda x: x.strftime("%W"))
table_Click2Delivery.head()


'''Посчитаем динамику по дням и сравним ее с метрикой PromisedClick2Delivery'''

days_Click2Delivery = table_Click2Delivery.groupby('date_order', as_index = False) \
                      .agg({'delivery_time': 'mean'})
days_Click2Delivery = days_Click2Delivery.merge(days_PromisedClick2Delivery, how = 'left', on = 'date_order') \
                      .rename(columns={'duration': 'planned_delivery'})
days_Click2Delivery.head()


df = pd.melt(days_Click2Delivery, id_vars=['date_order'], value_vars=['delivery_time', 'planned_delivery'])
fig = px.line(df, x='date_order', y='value', color='variable')
fig.show()


'''Посчитаем динамику по месяцам и сравним ее с метрикой PromisedClick2Delivery'''

month_Click2Delivery = table_Click2Delivery.groupby('month', as_index = False) \
                      .agg({'delivery_time': 'mean'})
month_Click2Delivery = month_Click2Delivery.merge(month_PromisedClick2Delivery, how = 'left', on = 'month') \
                      .rename(columns={'duration': 'planned_delivery'})
month_Click2Delivery.head()


df = pd.melt(month_Click2Delivery, id_vars=['month'], value_vars=['delivery_time', 'planned_delivery'])
fig = px.line(df, x='month', y='value', color='variable')
fig.show()


'''И, наконец, по неделям'''

week_Click2Delivery = table_Click2Delivery.groupby('week', as_index = False) \
                      .agg({'delivery_time': 'mean'})
week_Click2Delivery = week_Click2Delivery.merge(week_PromisedClick2Delivery, how = 'left', on = 'week') \
                      .rename(columns={'duration': 'planned_delivery'})
week_Click2Delivery.head()


df = pd.melt(week_Click2Delivery, id_vars=['week'], value_vars=['delivery_time', 'planned_delivery'])
fig = px.line(df, x='week', y='value', color='variable')
fig.show()


'''Вот таблица из прошлого задания, в которой видно обещанные сроки доставки из
кластера в кластер'''

PromisedClick2Delivery_clusters = PromisedClick2Delivery_clusters.reset_index()
PromisedClick2Delivery_clusters.head()


'''Сделаем такую же для фактического срока доставки'''

Click2Delivery_clusters = table_Click2Delivery.groupby(['warehouse_cluster', 'customer_cluster']) \
.agg({'delivery_time': 'mean'}).reset_index()
Click2Delivery_clusters.head()


'''И смерджим их'''

delivery_clusters = Click2Delivery_clusters.merge(PromisedClick2Delivery_clusters,
                             how = 'left', on = ['warehouse_cluster', 'customer_cluster']) \
                            .rename(columns={'duration': 'planned_delivery_time'})
delivery_clusters.head()


'''Оставим только те строки, где фактическое время доставки меньше планового'''

delivery_clusters = delivery_clusters[delivery_clusters['delivery_time'] 
                                      < delivery_clusters['planned_delivery_time']]
delivery_clusters.head()


q = '''
    SELECT *
    FROM 
        {db}.product_online
    '''


'''Информацию о суммах заказов будем брать из таблицы product_online'''

product_online = ph.read_clickhouse(query=q, connection=connection_default)
product_online.head()


pandas_profiling.ProfileReport(product_online)

Summarize dataset:   0%|          | 0/18 [00:00<?, ?it/s]

Generate report structure:   0%|          | 0/1 [00:00<?, ?it/s]

Render HTML:   0%|          | 0/1 [00:00<?, ?it/s]


'''Перемножив цену на количество товара, получим столбец с суммой по каждому товару в заказе'''

product_online['product_cost'] = product_online['amount'] * product_online['product_price']
product_online.head()


'''И теперь посчитаем стоимость каждого заказа'''

product_online = product_online.groupby('number_order', as_index = False) \
                .agg({'product_cost': 'sum'}).rename(columns = {'product_cost': 'order_sum'})
product_online.head()


'''Далее нам нужна первая таблица, но нужно удалить из нее лишнее'''

logistics_online.shape

(6596, 11)


'''В контексте данного задания нас интересует дата заказа. Помним, что часто дата заказа
проставляется только в строке с его номером и первым таймслотом. Отсортируем дф по дате заказа,
таким образом все даты, в которых стоит NaN, опустятся в конец дф. Затем отсортируем на всякий
случай по возрастанию таймслота и удалим дубликаты номеров заказов.Можем так сделать,
поскольку нас интересуют уникальные заказы, а в дф часто один заказ встречается несколько раз.'''

table_for_GMV_Accepted = logistics_online.sort_values(['date_order', 'timeslot_number']) \
.drop_duplicates(subset = 'number_order', keep = 'first')
table_for_GMV_Accepted.head()


'''Все равно остался 41 заказ без даты, это менее одного процента данных, можно их удалить'''

table_for_GMV_Accepted.isna().sum()

date_order             41
number_order            0
planned_data_order      0
timeslot_number         0
result_data_order     860
delivery_type           0
carrier_name            0
seller_ID               0
order_status            0
customer_cluster        0
warehouse_cluster       0
dtype: int64


table_for_GMV_Accepted = table_for_GMV_Accepted[table_for_GMV_Accepted['date_order'].notna()]


'''Теперь можем смерджить две таблицы, таким образом получив стоимость всех заказов'''

GMV_Accepted_merged = table_for_GMV_Accepted.merge(product_online, how = 'left',
                                                  on = 'number_order')
GMV_Accepted_merged.head()


'''Все хорошо, суммы есть для всех заказов'''

GMV_Accepted_merged.isna().sum()

date_order              0
number_order            0
planned_data_order      0
timeslot_number         0
result_data_order     841
delivery_type           0
carrier_name            0
seller_ID               0
order_status            0
customer_cluster        0
warehouse_cluster       0
order_sum               0
dtype: int64


'''Всё, можно формировать финальный датафрейм'''

GMV_Accepted = GMV_Accepted_merged.groupby(['date_order', 'carrier_name',
                             'delivery_type', 'warehouse_cluster'], as_index = False) \
                             .agg({'order_sum': 'sum'})
GMV_Accepted.head()


'''Динамика по датам заказа. Не слишком информативный график,
но благодаря возможностям plotly можно просто 'приблизить' нужный период'''

fig = px.line(GMV_Accepted, x="date_order", y="order_sum")
fig.show()


'''Динамика в разрезе перевозчиков'''

fig = px.line(GMV_Accepted, x="date_order", y="order_sum", color = 'carrier_name')
fig.show()


'''Динамика в разрезе типов доставки'''

fig = px.line(GMV_Accepted, x="date_order", y="order_sum", color = 'delivery_type')
fig.show()


'''Динамика в разрезе кластеров склада'''

fig = px.line(GMV_Accepted, x="date_order", y="order_sum", color = 'warehouse_cluster')
fig.show()


q = '''
    SELECT *
    FROM 
        {db}.returns_online
    '''


'''Видим, что в таблице с возвратами есть некорректные даты.
Это не проблема pandahouse, они такие в бд'''

returns_online = ph.read_clickhouse(query=q, connection=connection_default)
returns_online.head()


pandas_profiling.ProfileReport(returns_online)

Summarize dataset:   0%|          | 0/18 [00:00<?, ?it/s]

Generate report structure:   0%|          | 0/1 [00:00<?, ?it/s]

Render HTML:   0%|          | 0/1 [00:00<?, ?it/s]


'''Разберемся с дубликатами'''

returns_online[returns_online.duplicated()]


'''Посмотрим на какой-нибудь. Действительно дубликат. Придется их удалять. В данном задании 
они ни на что не влияют, а вот в следующем это будет важно'''

returns_online[returns_online['number_order']==3131450699]


returns_online = returns_online.drop_duplicates()


'''Вернемся к датам. Это тоже придется удалять. 181 строка'''

returns_online[returns_online['return_date'] == '1970-01-01'].head()


'''Получаем корректный датафрейм с возвратами. Без дубликатов и некорректных дат'''

returns_online = returns_online[returns_online['return_date'] != '1970-01-01']
returns_online.head()


returns_number = returns_online.groupby('return_date', as_index = False).agg({'number_order': 'nunique'}) \
                .rename(columns={'number_order': 'returns_number'})
returns_number.head()


'''Посмотрим, что получилось. Ну, от 1 до 14 возвратов вполне может быть. Почему-то пики всегда 3 числа)'''

fig = px.line(returns_number, x="return_date", y="returns_number")
fig.show()


'''Теперь чтобы понимать для каждого возврата перевозчика, кластеры склада и клиента,
нужно примерджить сюда таблицу с заказами и оставить только нужные колонки'''

full_returns = returns_online.merge(logistics_online, how = 'left', on = 'number_order')
full_returns_t = full_returns[['return_date', 'number_order', 'carrier_name', 'customer_cluster',
                             'warehouse_cluster']]
full_returns_t.head()


full_returns_final = full_returns_t.groupby(['return_date', 'carrier_name',
                                              'customer_cluster', 'warehouse_cluster'],
                                                      as_index = False) \
                                              .agg({'number_order': 'nunique'}) \
                                              .rename(columns={'number_order': 'returns_number'})
full_returns_final.head()


'''Возьмем таблицу из предыдущего задания. Это просто все имеющиеся заказы без дублей
и без NaN значений'''

table_for_GMV_Accepted.head()


'''Сгруппируем ее как просят в задании'''

table_for_Return_Rate = table_for_GMV_Accepted.groupby(['date_order', 'carrier_name',
                                                       'customer_cluster', 'warehouse_cluster'],
                                                      as_index = False) \
                                              .agg({'number_order': 'nunique'}) \
                                              .rename(columns={'number_order': 'order_number'})
table_for_Return_Rate.head()


'Сформируем финальный датафрейм. В нем можно посмотреть данные во всех запрашиваемых разрезах'

Return_Rate = pd.merge(table_for_Return_Rate, full_returns_final, how = 'left',
         left_on = ['date_order', 'carrier_name', 'customer_cluster', 'warehouse_cluster'],
         right_on = ['return_date', 'carrier_name', 'customer_cluster', 'warehouse_cluster']) \
         .fillna(0).drop('return_date', axis = 1)
Return_Rate['r_r_%'] = Return_Rate['returns_number'] *100 / Return_Rate['order_number']
Return_Rate.head()


'''Посмотрим динамику по дням на графике'''

dates_Return_Rate = table_for_GMV_Accepted.groupby('date_order', as_index = False) \
                    .agg({'number_order': 'nunique'}) \
                    .rename(columns={'number_order': 'order_number'}) \
                    .merge(returns_number, how = 'left', left_on = 'date_order',
                          right_on = 'return_date').drop('return_date', axis = 1) \
                    .fillna(0)
dates_Return_Rate['r_r'] = dates_Return_Rate['returns_number'] * 100 / dates_Return_Rate['order_number']
dates_Return_Rate.head()


'''Огромный всплеск 3 апреля'''

fig = px.line(dates_Return_Rate, x="date_order", y="r_r")
fig.show()


'''466 процентов'''

dates_Return_Rate[dates_Return_Rate['date_order']=='2021-04-03']


'''убедимся, что ошибка не у нас в расчетах. В этот день было 3 заказа'''

logistics_online[logistics_online['date_order']=='2021-04-03']


'''И 14 возвратов. Всё верно'''

returns_online[returns_online['return_date']=='2021-04-03']['number_order'].nunique()

14


'''Динамика в разрезе поставщиков'''

carriers_Return_Rate = Return_Rate.groupby(['date_order', 'carrier_name'], as_index = False) \
                        .agg({'order_number': 'sum', 'returns_number': 'sum'})
carriers_Return_Rate['r_r'] = carriers_Return_Rate['returns_number'] * 100 / carriers_Return_Rate['order_number']
carriers_Return_Rate.head()


'Всплеск из-за самодоставки'

fig = px.line(carriers_Return_Rate, x="date_order", y="r_r", color = 'carrier_name')
fig.show()


'''Динамика в разрезе кластеров склада'''

warehouse_Return_Rate = Return_Rate.groupby(['date_order', 'warehouse_cluster'], as_index = False) \
                        .agg({'order_number': 'sum', 'returns_number': 'sum'})
warehouse_Return_Rate['r_r'] = warehouse_Return_Rate['returns_number'] * 100 / warehouse_Return_Rate['order_number']
warehouse_Return_Rate.head()


fig = px.line(warehouse_Return_Rate, x="date_order", y="r_r", color = 'warehouse_cluster')
fig.show()


'''Динамика в разрезе кластеров покупателя'''

customer_Return_Rate = Return_Rate.groupby(['date_order', 'customer_cluster'], as_index = False) \
                        .agg({'order_number': 'sum', 'returns_number': 'sum'})
customer_Return_Rate['r_r'] = customer_Return_Rate['returns_number'] * 100 / customer_Return_Rate['order_number']
customer_Return_Rate.head()


fig = px.line(customer_Return_Rate, x="date_order", y="r_r", color = 'customer_cluster')
fig.show()


'''Возьмем уже готовый датафрейм из первого задания с доставленными заказами.'''

logistics_online_unique.head()


'''И будет нужна таблица с суммой каждого заказа'''

product_online.head()


'''Смерджим их и получим сумму по каждому заказу'''

orders_sum = logistics_online_unique.merge(product_online, how = 'left', on = 'number_order')
orders_sum.head()


'''Сделаем из него сразу основу для финального датафрейма'''

table_for_GMV_D_R = orders_sum.groupby(['result_data_order', 'carrier_name',
                                                     'delivery_type', 'warehouse_cluster'],
                                                   as_index = False) \
                    .agg({'order_sum': 'sum'}) \
                    .rename(columns = {'result_data_order': 'date'})
table_for_GMV_D_R.head()


'''Теперь будет нужна таблица с возвратами. Необходимо узнать сумму каждого возврата'''

returns_online.head()


'''Для этого снова нужна таблица product_online, в ней есть цены для каждого товара. Считаем ее заново'''

q = '''
    SELECT *
    FROM 
        {db}.product_online
    '''


products_for_GMV_DR = ph.read_clickhouse(query=q, connection=connection_default)
products_for_GMV_DR.head()


'''Получим сумму каждого возврата'''

returns_for_GMV_D_R = returns_online.merge(products_for_GMV_DR,
                                           how = 'left', on = ['number_order', 'product_ID']) \
                     .drop('amount', axis = 1) 
returns_for_GMV_D_R['return_sum'] = returns_for_GMV_D_R['amount_return'] * returns_for_GMV_D_R['product_price']
returns_for_GMV_D_R = returns_for_GMV_D_R.groupby(['return_date', 'number_order']
                                                  , as_index = False).agg({'return_sum': 'sum'})
returns_for_GMV_D_R.head()


'''Теперь нужно для каждого возврата узнать перевозчика, тип доставки и кластер склада'''

returns_for_GMV_D_R = returns_for_GMV_D_R.merge(logistics_online, how = 'left', on = 'number_order')
returns_for_GMV_D_R = returns_for_GMV_D_R[['return_date', 'number_order', 'carrier_name',
                                         'delivery_type', 'warehouse_cluster', 'return_sum']]
returns_for_GMV_D_R.head()


'''Теперь надо возвраты сгруппировать по нужным признакам'''

returns_for_GMV_D_R = returns_for_GMV_D_R.groupby(['return_date', 'carrier_name', 'delivery_type',
                       'warehouse_cluster'], as_index = False) \
                    .agg({'return_sum': 'sum'}) \
                    .rename(columns = {'return_date': 'date'})
returns_for_GMV_D_R.head()


table_for_GMV_D_R.head()


'''Вот итоговый датафрейм в разрезе по всем запрашиваемым признакам'''

GMV_D_R = table_for_GMV_D_R.merge(returns_for_GMV_D_R, how = 'left',
                                 on = ['date', 'carrier_name', 'delivery_type', 'warehouse_cluster']) \
                                .fillna(0)
GMV_D_R['gmv_dr'] = GMV_D_R['order_sum'] - GMV_D_R['return_sum']
GMV_D_R.head()


'''Теперь посмотрим динамику по дням'''

fig = px.line(GMV_D_R, x="date", y="gmv_dr")
fig.show()


'''Динамика по дням в разрезе перевозчиков'''

fig = px.line(GMV_D_R, x="date", y="gmv_dr", color = 'carrier_name')
fig.show()


'''Динамика по дням в разрезе типов доставки'''

fig = px.line(GMV_D_R, x="date", y="gmv_dr", color = 'delivery_type')
fig.show()


'''Динамика по дням в разрезе кластеров склада. Лучшие показатели у кластеров Москва и Екатеринбург,
что не удивительно, поскольку это два крупных города, в которых, вероятно, большие заказы'''

fig = px.line(GMV_D_R, x="date", y="gmv_dr", color = 'warehouse_cluster')
fig.show()

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster
0	2021-5-01	313655114	2021-14-01	1	2021-11-01	Самовывоз	3PL 1	301202	Доставлен	Кластер Краснодар	Кластер Москва
1	2021-3-01	315229153	2021-11-01	1	2021-8-01	Курьер	Самодоставка	40560	Доставлен	Кластер Московская область	Кластер Московская область
2	2021-8-01	3114106095	2021-16-01	1	2021-14-01	Курьер	Самодоставка	40560	Доставлен	Кластер Московская область	Кластер Московская область
3	2021-12-01	31145541407	2021-20-01	1	2021-15-01	Самовывоз	3PL 1	301202	Доставлен	Кластер Санкт-Петербург	Кластер Москва
4	2021-16-01	325050136	2021-26-01	1	2021-25-01	Самовывоз	3PL 1	946233	Доставлен	Кластер Санкт-Петербург	Кластер Санкт-Петербург

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster
0	2021-01-05	313655114	2021-01-14	1	2021-01-11	Самовывоз	3PL 1	301202	Доставлен	Кластер Краснодар	Кластер Москва
1	2021-01-03	315229153	2021-01-11	1	2021-01-08	Курьер	Самодоставка	40560	Доставлен	Кластер Московская область	Кластер Московская область
2	2021-01-08	3114106095	2021-01-16	1	2021-01-14	Курьер	Самодоставка	40560	Доставлен	Кластер Московская область	Кластер Московская область
3	2021-01-12	31145541407	2021-01-20	1	2021-01-15	Самовывоз	3PL 1	301202	Доставлен	Кластер Санкт-Петербург	Кластер Москва
4	2021-01-16	325050136	2021-01-26	1	2021-01-25	Самовывоз	3PL 1	946233	Доставлен	Кластер Санкт-Петербург	Кластер Санкт-Петербург

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster
726	2021-02-16	344629456	2021-02-26	1	2021-03-03	Курьер	3PL 1	9504	Доставлен	Кластер Калининград	Кластер Казань
727	NaT	344629456	2021-03-03	2	2021-03-03	Курьер	3PL 1	9504	Доставлен	Кластер Калининград	Кластер Казань
728	2021-02-16	344629456	2021-02-26	1	2021-03-03	Курьер	3PL 1	9504	Доставлен	Кластер Калининград	Кластер Казань

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster
11	NaT	3366721496	2021-02-16	2	2021-02-15	Самовывоз	3PL 3	94320	Доставлен	Кластер Екатеринбург	Кластер Московская область
15	NaT	337626360	2021-02-19	2	2021-02-16	Самовывоз	3PL 3	5038	Доставлен	Кластер Санкт-Петербург	Кластер Центр
20	NaT	342545229	2021-02-19	2	2021-02-19	Курьер	Самодоставка	40560	Доставлен	Кластер Москва	Кластер Московская область
21	NaT	346099025	2021-03-15	2	2021-03-05	Курьер	3PL 1	629123	Доставлен	Кластер Московская область	Кластер Санкт-Петербург
23	NaT	34726147014	2021-03-03	2	2021-03-02	Курьер	3PL 1	202312	Доставлен	Кластер Краснодар	Кластер Краснодар

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster
204	NaT	301939627	2021-01-03	1	NaT	Самовывоз	3PL 2	68503	Доставлен	Кластер Екатеринбург	Кластер Москва
462	2021-01-03	311361912	2021-01-12	1	NaT	Курьер	Самодоставка	2619	Доставлен	Кластер Москва	Кластер Москва
874	2021-01-07	3101420316	2021-01-11	1	NaT	Курьер	Самодоставка	2619	Доставлен	Кластер Москва	Кластер Москва
1007	2021-01-03	3069631404	2021-01-14	1	NaT	Самовывоз	3PL 2	68503	Доставлен	Кластер Калининград	Кластер Москва
1100	NaT	305674342	2021-01-07	1	NaT	Курьер	Самодоставка	833469	Доставлен	Кластер Екатеринбург	Кластер Екатеринбург

Задание 3¶

Метрики — показатели, с помощью которых измеряют эффективность процессов на каждом этапе.¶

	result_data_order	carrier_name	delivery_type	warehouse_cluster	timeslot_number	ontime
3	2021-01-04	3PL 2	Самовывоз	Кластер Екатеринбург	1	100.0
4	2021-01-04	3PL 2	Самовывоз	Кластер Екатеринбург	2	100.0
5	2021-01-04	3PL 2	Самовывоз	Кластер Москва	1	0.0
6	2021-01-04	Самодоставка	Курьер	Кластер Московская область	1	100.0

	result_data_order	ontime
0	2021-01-02	100.000000
1	2021-01-03	100.000000
2	2021-01-04	92.857143
3	2021-01-05	90.000000
4	2021-01-06	100.000000

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster
13	2021-02-11	336693909	2021-02-16	1	2021-02-17	Самовывоз	3PL 3	2345336	Доставлен	Кластер Екатеринбург	Кластер Московская область
450	2021-03-05	357469624	2021-03-12	1	NaT	Курьер	3PL 3	8612129	Отменен	Кластер Краснодар	Кластер Москва
6427	2021-02-06	333776216	2021-02-11	1	2021-02-13	Самовывоз	3PL 1	633129	Доставлен	Кластер Санкт-Петербург	Кластер Екатеринбург

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster
6426	2021-02-05	333776216	2021-02-11	1	2021-02-13	Самовывоз	3PL 1	633129	Доставлен	Кластер Санкт-Петербург	Кластер Екатеринбург
6427	2021-02-06	333776216	2021-02-11	1	2021-02-13	Самовывоз	3PL 1	633129	Доставлен	Кластер Санкт-Петербург	Кластер Екатеринбург

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster
12	2021-02-09	336693909	2021-02-16	1	2021-02-17	Самовывоз	3PL 3	2345336	Доставлен	Кластер Екатеринбург	Кластер Московская область
13	2021-02-11	336693909	2021-02-16	1	2021-02-17	Самовывоз	3PL 3	2345336	Доставлен	Кластер Екатеринбург	Кластер Московская область

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster
449	2021-03-04	357469624	2021-03-12	1	NaT	Курьер	3PL 3	8612129	Отменен	Кластер Краснодар	Кластер Москва
450	2021-03-05	357469624	2021-03-12	1	NaT	Курьер	3PL 3	8612129	Отменен	Кластер Краснодар	Кластер Москва

	month	duration
0	2021-01	7.705836
1	2021-02	7.626955
2	2021-03	7.233554
3	2021-04	6.900000

	date_order	duration
0	2021-01-01	9.285714
1	2021-01-02	9.176471
2	2021-01-03	8.840000
3	2021-01-04	8.625000
4	2021-01-05	8.333333

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster	ontime
920	NaT	312463476	2021-01-05	1	2021-01-02	Курьер	Самодоставка	2619	Доставлен	Кластер Москва	Кластер Москва	1
918	2021-01-07	311714051	2021-01-14	1	2021-01-03	Курьер	Самодоставка	40560	Доставлен	Кластер Московская область	Кластер Московская область	1
1676	2021-01-04	3109561443	2021-01-14	1	2021-01-03	Самовывоз	3PL 2	833469	Доставлен	Кластер Казань	Кластер Екатеринбург	1
2144	2021-01-05	3101445723	2021-01-12	1	2021-01-03	Самовывоз	3PL 2	989330	Доставлен	Кластер Санкт-Петербург	Кластер Екатеринбург	1
3342	2021-01-10	311729202	2021-01-14	1	2021-01-03	Курьер	Самодоставка	40560	Доставлен	Кластер Московская область	Кластер Московская область	1

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster	ontime
6070	2021-03-03	3514710433	2021-03-11	1	2021-03-15	Курьер	3PL 3	19923	Доставлен	Кластер Центр	Кластер Московская область	0
6067	NaT	3514710433	2021-03-12	2	2021-03-15	Курьер	3PL 3	19923	Доставлен	Кластер Центр	Кластер Московская область	0
6069	NaT	3514710433	2021-03-13	3	2021-03-15	Курьер	3PL 3	19923	Доставлен	Кластер Центр	Кластер Московская область	0
6068	NaT	3514710433	2021-03-15	4	2021-03-15	Курьер	3PL 3	19923	Доставлен	Кластер Центр	Кластер Московская область	1

	week	duration
0	00	9.053571
1	01	7.395062
2	02	7.689362
3	03	7.780000
4	04	7.663594

	week	delivery_time	planned_delivery
0	00	7.272727	9.053571
1	01	6.308824	7.395062
2	02	6.203046	7.689362
3	03	6.037175	7.780000
4	04	5.815385	7.663594

	number_order	product_ID	product_price	amount
0	313655114	983587801	5512.44	1
1	313655114	983587158	4070.35	2
2	313655114	983582669	3591.97	1
3	315229153	983589424	4433.23	3
4	315229153	983587827	2862.50	2

	number_order	product_ID	product_price	amount	product_cost
0	313655114	983587801	5512.44	1	5512.44
1	313655114	983587158	4070.35	2	8140.70
2	313655114	983582669	3591.97	1	3591.97
3	315229153	983589424	4433.23	3	13299.69
4	315229153	983587827	2862.50	2	5725.00

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster	ontime
4947	2021-01-01	312307142	2021-01-08	1	2021-01-04	Курьер	Самодоставка	40560	Доставлен	Кластер Москва	Кластер Московская область	1
3376	2021-01-01	3132714147	2021-01-12	1	2021-01-06	Самовывоз	3PL 2	41958	Доставлен	Кластер Санкт-Петербург	Кластер Казань	1
4144	2021-01-01	3141410151	2021-01-10	1	2021-01-08	Курьер	Самодоставка	40560	Доставлен	Кластер Москва	Кластер Московская область	1
5930	2021-01-01	314096365	2021-01-09	1	2021-01-08	Курьер	Самодоставка	40560	Доставлен	Кластер Московская область	Кластер Московская область	1
307	2021-01-01	316360643	2021-01-12	1	2021-01-10	Курьер	Самодоставка	2619	Доставлен	Кластер Москва	Кластер Москва	1

	date_order	delivery_time	planned_delivery
0	2021-01-01	8.000000	9.285714
1	2021-01-02	7.571429	9.176471
2	2021-01-03	6.700000	8.840000
3	2021-01-04	6.000000	8.625000
4	2021-01-05	7.545455	8.333333

	month	delivery_time	planned_delivery
0	2021-01	6.087862	7.705836
1	2021-02	6.324035	7.626955
2	2021-03	5.228602	7.233554

	warehouse_cluster	customer_cluster	duration
0	Кластер Екатеринбург	Кластер Екатеринбург	7.505140
1	Кластер Екатеринбург	Кластер Казань	7.445946
2	Кластер Екатеринбург	Кластер Калининград	7.500000
3	Кластер Екатеринбург	Кластер Краснодар	7.614286
4	Кластер Екатеринбург	Кластер Москва	7.446809

	warehouse_cluster	customer_cluster	delivery_time
0	Кластер Екатеринбург	Кластер Екатеринбург	2.187500
1	Кластер Екатеринбург	Кластер Казань	3.434783
2	Кластер Екатеринбург	Кластер Калининград	4.500000
3	Кластер Екатеринбург	Кластер Краснодар	3.843137
4	Кластер Екатеринбург	Кластер Москва	5.447368

	number_order	order_sum
0	300232595	13049.90
1	300612175	21582.16
2	301939627	16359.44
3	302073096	13328.32
4	302459167	8701.56

	number_order	product_ID	amount_return	return_date
0	3031410942	983588011	1	1970-01-01
1	31014374140	983586554	1	2021-07-01
2	3147145173	983589156	1	2021-11-01
3	3141464669	983589362	1	2021-11-01
4	3131450699	983589729	3	2021-12-01

	return_date	returns_number
0	2021-01-02	7
1	2021-01-03	1
2	2021-02-02	5
3	2021-02-03	5
4	2021-03-02	1

	date_order	order_number	returns_number	r_r
0	2021-01-01	14	0.0	0.000000
1	2021-01-02	17	7.0	41.176471
2	2021-01-03	25	1.0	4.000000
3	2021-01-04	32	0.0	0.000000
4	2021-01-05	27	0.0	0.000000

	date_order	number_order	planned_data_order	timeslot_number	result_data_order	delivery_type	carrier_name	seller_ID	order_status	customer_cluster	warehouse_cluster
969	2021-04-03	3651761467	2021-04-10	1	NaT	Курьер	Самодоставка	2312360	Ожидает сборки	Кластер Краснодар	Кластер Московская область
1837	2021-04-03	3514914196	2021-04-10	1	NaT	Курьер	Самодоставка	120629	На пути к клиенту	Кластер Краснодар	Кластер Екатеринбург
2133	2021-04-03	35623141440	2021-04-13	1	NaT	Курьер	Самодоставка	120629	На пути к клиенту	Кластер Краснодар	Кластер Екатеринбург

	return_date	number_order	return_sum
0	2021-01-02	326234700	6166.35
1	2021-01-02	327260705	4073.99
2	2021-01-02	3267153714	4382.22
3	2021-01-02	3295391450	9486.65
4	2021-01-02	32146147293	6078.12